USE [influenza]
GO

/****** Object:  StoredProcedure [dbo].[SP_RptGrafSexo]    Script Date: 03/09/2010 19:09:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[SP_RptGrafSexo]
@param varchar(100)

as
Begin

declare 
@qry varchar(8000),
@anio varchar(4),
@tipo varchar(1),
@laboratorio varchar(5),
@pand varchar(10)

set @anio=YEAR(GETDATE())

set @tipo= dbo.fnSplit(@param,'|',1)
set @laboratorio= dbo.fnSplit(@param,'|',2)

if @tipo=1
begin
	set @pand='in (1)'
end
else if @tipo=2
begin
	set @pand='not in (1,5)'
end
else if @tipo=3
begin
	set @pand='in (5)'
end


SET @qry='
Select ''0 a 1'' as Rango,[MASCULINO] as ''Hombres'',[FEMENINO] as ''Mujeres'',(MASCULINO+FEMENINO) as TOTAL from (
	Select ''0 a 1'' as Rango,c.cSexo as Sexo
	 from dat_generales a
	 inner join dat_laboratorio b on(a.id_principal=b.id_generales)
	 inner join cat_sexo c on(a.nSexo=c.id_principal)
	 where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) between 0 and 1 and 
	Year(fSintoma_inicial) between '+@anio+' and '+@anio+' and
	nResultado '+@pand
 if @laboratorio>0
  begin
    set @qry=@qry+' and b.id_laboratorio='+@laboratorio
  end
set @qry=@qry+'
 )pvt
 PIVOT( count(Rango) for sexo in ([MASCULINO],[FEMENINO]))as child
 union all
Select ''1 a 4'' as Rango,[MASCULINO] as ''Hombres'',[FEMENINO] as ''Mujeres'',(MASCULINO+FEMENINO) as TOTAL from (
	Select ''1 a 4'' as Rango,c.cSexo as Sexo
	 from dat_generales a
	 inner join dat_laboratorio b on(a.id_principal=b.id_generales)
	 inner join cat_sexo c on(a.nSexo=c.id_principal)
	 where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) between 1 and 4 and 
	Year(fSintoma_inicial) between '+@anio+' and '+@anio+' and
	nResultado '+@pand
 if @laboratorio>0
  begin
    set @qry=@qry+' and b.id_laboratorio='+@laboratorio
  end
set @qry=@qry+'
 )pvt
 PIVOT( count(Rango) for sexo in ([MASCULINO],[FEMENINO]))as child
 union all 
Select ''5 a 9'' as Rango,[MASCULINO] as ''Hombres'',[FEMENINO] as ''Mujeres'',(MASCULINO+FEMENINO) as TOTAL from (
	Select ''5 a 9'' as Rango,c.cSexo as Sexo
	 from dat_generales a
	 inner join dat_laboratorio b on(a.id_principal=b.id_generales)
	 inner join cat_sexo c on(a.nSexo=c.id_principal)
	 where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) between 5 and 9 and 
	Year(fSintoma_inicial) between '+@anio+' and '+@anio+' and
	nResultado '+@pand
 if @laboratorio>0
  begin
    set @qry=@qry+' and b.id_laboratorio='+@laboratorio
  end
set @qry=@qry+'
 )pvt
 PIVOT( count(Rango) for sexo in ([MASCULINO],[FEMENINO]))as child
 union all
Select ''10 a 19'' as Rango,[MASCULINO] as ''Hombres'',[FEMENINO] as ''Mujeres'',(MASCULINO+FEMENINO) as TOTAL from (
	Select ''10 a 19'' as Rango,c.cSexo as Sexo
	 from dat_generales a
	 inner join dat_laboratorio b on(a.id_principal=b.id_generales)
	 inner join cat_sexo c on(a.nSexo=c.id_principal)
	 where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) between 10 and 19 and 
	Year(fSintoma_inicial) between '+@anio+' and '+@anio+' and
	nResultado '+@pand
 if @laboratorio>0
  begin
    set @qry=@qry+' and b.id_laboratorio='+@laboratorio
  end
set @qry=@qry+'
 )pvt
 PIVOT( count(Rango) for sexo in ([MASCULINO],[FEMENINO]))as child
 union all  
 Select ''20 a 29'' as Rango,[MASCULINO] as ''Hombres'',[FEMENINO] as ''Mujeres'',(MASCULINO+FEMENINO) as TOTAL from (
	Select ''20 a 29'' as Rango,c.cSexo as Sexo
	 from dat_generales a
	 inner join dat_laboratorio b on(a.id_principal=b.id_generales)
	 inner join cat_sexo c on(a.nSexo=c.id_principal)
	 where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) between 20 and 29 and 
	Year(fSintoma_inicial) between '+@anio+' and '+@anio+' and
	nResultado '+@pand
 if @laboratorio>0
  begin
    set @qry=@qry+' and b.id_laboratorio='+@laboratorio
  end
set @qry=@qry+'
 )pvt
 PIVOT( count(Rango) for sexo in ([MASCULINO],[FEMENINO]))as child
 union all
 Select ''30 a 39'' as Rango,[MASCULINO] as ''Hombres'',[FEMENINO] as ''Mujeres'',(MASCULINO+FEMENINO) as TOTAL from (
	Select ''30 a 39'' as Rango,c.cSexo as Sexo
	 from dat_generales a
	 inner join dat_laboratorio b on(a.id_principal=b.id_generales)
	 inner join cat_sexo c on(a.nSexo=c.id_principal)
	 where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) between 30 and 39 and 
	Year(fSintoma_inicial) between '+@anio+' and '+@anio+' and
	nResultado '+@pand
 if @laboratorio>0
  begin
    set @qry=@qry+' and b.id_laboratorio='+@laboratorio
  end
set @qry=@qry+'
 )pvt
 PIVOT( count(Rango) for sexo in ([MASCULINO],[FEMENINO]))as child
 union all 
Select ''40 a 49'' as Rango,[MASCULINO] as ''Hombres'',[FEMENINO] as ''Mujeres'',(MASCULINO+FEMENINO) as TOTAL from (
	Select ''40 a 49'' as Rango,c.cSexo as Sexo
	 from dat_generales a
	 inner join dat_laboratorio b on(a.id_principal=b.id_generales)
	 inner join cat_sexo c on(a.nSexo=c.id_principal)
	 where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) between 40 and 49 and 
	Year(fSintoma_inicial) between '+@anio+' and '+@anio+' and
	nResultado '+@pand
 if @laboratorio>0
  begin
    set @qry=@qry+' and b.id_laboratorio='+@laboratorio
  end
set @qry=@qry+'
 )pvt
 PIVOT( count(Rango) for sexo in ([MASCULINO],[FEMENINO]))as child
 union all
 Select ''50 a 59'' as Rango,[MASCULINO] as ''Hombres'',[FEMENINO] as ''Mujeres'',(MASCULINO+FEMENINO) as TOTAL from (
	Select ''50 a 59'' as Rango,c.cSexo as Sexo
	 from dat_generales a
	 inner join dat_laboratorio b on(a.id_principal=b.id_generales)
	 inner join cat_sexo c on(a.nSexo=c.id_principal)
	 where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) between 50 and 59 and 
	Year(fSintoma_inicial) between '+@anio+' and '+@anio+' and
	nResultado '+@pand
 if @laboratorio>0
  begin
    set @qry=@qry+' and b.id_laboratorio='+@laboratorio
  end
set @qry=@qry+'
 )pvt
 PIVOT( count(Rango) for sexo in ([MASCULINO],[FEMENINO]))as child
 union all  
 Select ''Mayor de 60'' as Rango,[MASCULINO] as ''Hombres'',[FEMENINO] as ''Mujeres'',(MASCULINO+FEMENINO) as TOTAL from (
	Select ''Mayor de 60'' as Rango,c.cSexo as Sexo
	 from dat_generales a
	 inner join dat_laboratorio b on(a.id_principal=b.id_generales)
	 inner join cat_sexo c on(a.nSexo=c.id_principal)
	 where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) > 59 and 
	Year(fSintoma_inicial) between '+@anio+' and '+@anio+' and
	nResultado '+@pand
 if @laboratorio>0
  begin
    set @qry=@qry+' and b.id_laboratorio='+@laboratorio
  end
set @qry=@qry+'
 )pvt
 PIVOT( count(Rango) for sexo in ([MASCULINO],[FEMENINO]))as child 
'
exec(@qry)
END


GO

